import pandas as pd

# 读取Excel文件
df = pd.read_excel('deal_data.xlsx', sheet_name='Sheet1')

def create_sql(row):
    settleType = row['TYPE']

    if settleType == 4:
        return f"update external_money_deal_detail set PAY_STATUS = '0{str(row['批次表支付状态'])}', DEAL_FEE = {row['累计结算金额']} where DEAL_ID = '{row['DEAL_ID']}';"
    else:
        return f"update external_money_deal_detail set PAY_STATUS = '0{str(row['批次表支付状态'])}', DEAL_FEE = {row['累计服务费']} where DEAL_ID = '{row['DEAL_ID']}';"


# 应用函数到DataFrame，生成新的列
df['SQL'] = df.apply(create_sql, axis=1)

# 如果需要保存到新的Excel文件
df.to_excel('处理后的deal_data.xlsx', index=False)